----------------------------------------------
-- @ Output：sany_dim.dim_prd_mtrl_clss_df
-- @ Desc：物料分类宽表（落地表）
-- @ Primary Key：tm_clas_id 
-- @ Author：xiaolc
-- @ Create Time：2021-04-19
-- @ Modify Time：2021-04-19
-- @ Comment：物料分类宽表
----------------------------------------------

--物料分类宽表（落地表）
-- 与物料宽表的关系：sany_dim.dwd_public_mdm_tp_df(tm_clas_id) = sany_dim.dim_prd_mtrl_clss_df(tm_clas_id)

-- DROP TABLE sany_dim.dim_prd_mtrl_clss_df;
CREATE TABLE IF NOT EXISTS sany_dim.dim_prd_mtrl_clss_df(
   tm_clas_id      string COMMENT '分类ID'
  ,depth       string COMMENT '层级'
  ,type_no      string COMMENT '分类编码'
  ,type_descs      string COMMENT '分类描述'
  
  ,descs_en  string COMMENT 'descs_en'
  ,material_type  string COMMENT '物料类型'
  ,parts_type  string COMMENT 'parts_type'
  ,key_degree  string COMMENT '关重件程度'
  ,enable  string COMMENT '是否启用（默认true启用，false逻辑删除'
  ,is_approved  string COMMENT 'is_approved'
  ,erp_status  string COMMENT 'erp_status'
  ,erp_message  string COMMENT 'erp_message'
  ,hide  string COMMENT '是否隐藏'
  ,from_id  string COMMENT '从哪里复制过来的'
  ,operation  string COMMENT 'operation'
  ,desc2  string COMMENT '长描述'
  ,note  string COMMENT '备注'
  ,is_temp  string COMMENT 'is_temp'
  ,parent_clas_id  string COMMENT '直系父类id'
  ,applicatoin_at  string COMMENT '接口发送时间'
  ,application_user  string COMMENT '接口发送人'
  ,create_user  string COMMENT 'create_user'
  ,create_time  string COMMENT 'create_time'
  ,update_user  string COMMENT 'update_user'
  ,update_time  string COMMENT 'update_time'
  ,opt_counter  string COMMENT 'opt_counter'
  ,is_to_pdm  string COMMENT '外购件是否集成到pdm'
  ,application_type  string COMMENT '物料申请类型（外购件分类，产成品分类等等）'
  ,application_type_belong  string COMMENT '所属申请类型'
  ,r_normt  string COMMENT '标准代号是否必填'
  ,r_net_weight  string COMMENT '重量是否必填'
  ,r_permission_group  string COMMENT '权限组是否必填'
  ,r_material_name  string COMMENT '物料名称是否必填'
  ,r_material_en_name  string COMMENT '物料英文名称是否必填'
  ,r_jsxy  string COMMENT '技术协议不签字版'
  ,r_jsxy_sign  string COMMENT '技术协议签字版'
  ,r_cpzl  string COMMENT 'r_cpzl'
  ,r_ckg  string COMMENT 'r_ckg'
  ,r_prod_level  string COMMENT '产品层次是否必填'
  ,status  string COMMENT '分类状态'
  ,formula  string COMMENT '规格合成公式'
  ,material_name_list  string COMMENT '物料名称列表'
  ,process_id  string COMMENT 'bpm流程id'
  ,prod_level_id  string COMMENT '产品层次'
  ,is_to_ibp  string COMMENT 'is_to_ibp'
  ,plant_id  string COMMENT 'plant_id'
  ,role_id  string COMMENT 'role_id'
  ,in_using  string COMMENT '是否正式使用'
  ,clas_group  string COMMENT '分类分组'
  ,view_name  string COMMENT '视图名称'
  ,product_level_names  string COMMENT '产品层次名称'
  ,purchase_tech_types  string COMMENT '采购技术要求'
  ,oid  string COMMENT '文件夹oid'
  ,is_zk  string COMMENT '该分类是否为重卡分类'
  
  ,level1_id      string COMMENT '一级分类ID'
  ,level1_desc     string COMMENT '一级分类描述'
  ,level2_id      string COMMENT '二级分类ID'
  ,level2_desc     string COMMENT '二级分类描述'
  ,level3_id      string COMMENT '三级分类ID'
  ,level3_desc     string COMMENT '三级分类描述'
  ,level4_id      string COMMENT '四级分类ID'
  ,level4_desc     string COMMENT '四级分类描述'
  ,etl_time       timestamp COMMENT 'etl任务完成时间'
 )
COMMENT '物料分类宽表'
partitioned by (ds string)
ROW FORMAT DELIMITED STORED AS orc TBLPROPERTIES('orc.compression'='SNAPPY')
;

insert overwrite table sany_dim.dim_prd_mtrl_clss_df PARTITION(ds = '${IncStartDate}') --按照逻辑关系最新输出表
 select 
  t1.tm_clas_id     --'分类ID'
  ,t1.depth      --'层级'
  ,t1.no AS type_no    --'分类编码'
  ,t1.descs AS type_descs   --'分类描述'
  ,t1.descs_en  -- string COMMENT ''descs_en
  ,t1.material_type  -- string COMMENT ''物料类型
  ,t1.parts_type  -- string COMMENT ''parts_type
  ,t1.key_degree  -- string COMMENT ''关重件程度
  ,t1.enable  -- string COMMENT ''是否启用（默认true启用，false逻辑删除
  ,t1.is_approved  -- string COMMENT ''is_approved
  ,t1.erp_status  -- string COMMENT ''erp_status
  ,t1.erp_message  -- string COMMENT ''erp_message
  ,t1.hide  -- string COMMENT ''是否隐藏
  ,t1.from_id  -- string COMMENT ''从哪里复制过来的
  ,t1.operation  -- string COMMENT ''operation
  ,t1.desc2  -- string COMMENT ''长描述
  ,t1.note  -- string COMMENT ''备注
  ,t1.is_temp  -- string COMMENT ''is_temp
  ,t1.parent_clas_id  -- string COMMENT ''直系父类id
  ,t1.applicatoin_at  -- string COMMENT ''接口发送时间
  ,t1.application_user  -- string COMMENT ''接口发送人
  ,t1.create_user  -- string COMMENT ''create_user
  ,t1.create_time  -- string COMMENT ''create_time
  ,t1.update_user  -- string COMMENT ''update_user
  ,t1.update_time  -- string COMMENT ''update_time
  ,t1.opt_counter  -- string COMMENT ''opt_counter
  ,t1.is_to_pdm  -- string COMMENT ''外购件是否集成到pdm
  ,t1.application_type  -- string COMMENT ''物料申请类型（外购件分类，产成品分类等等）
  ,t1.application_type_belong  -- string COMMENT ''所属申请类型
  ,t1.r_normt  -- string COMMENT ''标准代号是否必填
  ,t1.r_net_weight  -- string COMMENT ''重量是否必填
  ,t1.r_permission_group  -- string COMMENT ''权限组是否必填
  ,t1.r_material_name  -- string COMMENT ''物料名称是否必填
  ,t1.r_material_en_name  -- string COMMENT ''物料英文名称是否必填
  ,t1.r_jsxy  -- string COMMENT ''技术协议不签字版
  ,t1.r_jsxy_sign  -- string COMMENT ''技术协议签字版
  ,t1.r_cpzl  -- string COMMENT ''r_cpzl
  ,t1.r_ckg  -- string COMMENT ''r_ckg
  ,t1.r_prod_level  -- string COMMENT ''产品层次是否必填
  ,t1.status  -- string COMMENT ''分类状态
  ,t1.formula  -- string COMMENT ''规格合成公式
  ,t1.material_name_list  -- string COMMENT ''物料名称列表
  ,t1.process_id  -- string COMMENT ''bpm流程id
  ,t1.prod_level_id  -- string COMMENT ''产品层次
  ,t1.is_to_ibp  -- string COMMENT ''is_to_ibp
  ,t1.plant_id  -- string COMMENT ''plant_id
  ,t1.role_id  -- string COMMENT ''role_id
  ,t1.in_using  -- string COMMENT ''是否正式使用
  ,t1.clas_group  -- string COMMENT ''分类分组
  ,t1.view_name  -- string COMMENT ''视图名称
  ,t1.product_level_names  -- string COMMENT ''产品层次名称
  ,t1.purchase_tech_types  -- string COMMENT ''采购技术要求
  ,t1.oid  -- string COMMENT ''文件夹oid
  ,t1.is_zk  -- string COMMENT ''该分类是否为重卡分类
  
  ,t2.no as level1_id     --'一级分类ID'
  ,t2.descs AS level1_desc  --'一级分类描述'
  ,t3.no as level2_id    --'二级分类ID'
  ,t3.descs AS level2_desc  --'二级分类描述'
  ,t4.no as level3_id     --'三级分类ID'
  ,t4.descs AS level3_desc  --'三级分类描述'
  ,t5.no as level4_id    --'四级分类ID'
  ,t5.descs as level4_desc     --string COMMENT '四级分类描述'
  ,current_timestamp as etl_time --'etl任务完成时间'
from

(select
 tm_clas_id
 ,no
 ,nvl(level1_id,tm_clas_id) as level1_id
 ,if(level1_id is not null and level2_id is null,tm_clas_id,level2_id) as level2_id --
 ,if(level2_id is not null and level3_id is null,tm_clas_id,level3_id) as level3_id
 ,if(level3_id is not null and level4_id is null,tm_clas_id,level4_id) as level4_id
 ,depth  -- string COMMENT ''层级
 ,descs  -- string COMMENT ''分类描述
 ,descs_en  -- string COMMENT ''descs_en
 ,material_type  -- string COMMENT ''物料类型
 ,parts_type  -- string COMMENT ''parts_type
 ,key_degree  -- string COMMENT ''关重件程度
 ,enable  -- string COMMENT ''是否启用（默认true启用，false逻辑删除
 ,is_approved  -- string COMMENT ''is_approved
 ,erp_status  -- string COMMENT ''erp_status
 ,erp_message  -- string COMMENT ''erp_message
 ,hide  -- string COMMENT ''是否隐藏
 ,from_id  -- string COMMENT ''从哪里复制过来的
 ,operation  -- string COMMENT ''operation
 ,desc2  -- string COMMENT ''长描述
 ,note  -- string COMMENT ''备注
 ,is_temp  -- string COMMENT ''is_temp
 ,parent_clas_id  -- string COMMENT ''直系父类id
 ,applicatoin_at  -- string COMMENT ''接口发送时间
 ,application_user  -- string COMMENT ''接口发送人
 ,create_user  -- string COMMENT ''create_user
 ,create_time  -- string COMMENT ''create_time
 ,update_user  -- string COMMENT ''update_user
 ,update_time  -- string COMMENT ''update_time
 ,opt_counter  -- string COMMENT ''opt_counter
 ,is_to_pdm  -- string COMMENT ''外购件是否集成到pdm
 ,application_type  -- string COMMENT ''物料申请类型（外购件分类，产成品分类等等）
 ,application_type_belong  -- string COMMENT ''所属申请类型
 ,r_normt  -- string COMMENT ''标准代号是否必填
 ,r_net_weight  -- string COMMENT ''重量是否必填
 ,r_permission_group  -- string COMMENT ''权限组是否必填
 ,r_material_name  -- string COMMENT ''物料名称是否必填
 ,r_material_en_name  -- string COMMENT ''物料英文名称是否必填
 ,r_jsxy  -- string COMMENT ''技术协议不签字版
 ,r_jsxy_sign  -- string COMMENT ''技术协议签字版
 ,r_cpzl  -- string COMMENT ''r_cpzl
 ,r_ckg  -- string COMMENT ''r_ckg
 ,r_prod_level  -- string COMMENT ''产品层次是否必填
 ,status  -- string COMMENT ''分类状态
 ,formula  -- string COMMENT ''规格合成公式
 ,material_name_list  -- string COMMENT ''物料名称列表
 ,process_id  -- string COMMENT ''bpm流程id
 ,prod_level_id  -- string COMMENT ''产品层次
 ,is_to_ibp  -- string COMMENT ''is_to_ibp
 ,plant_id  -- string COMMENT ''plant_id
 ,role_id  -- string COMMENT ''role_id
 ,in_using  -- string COMMENT ''是否正式使用
 ,clas_group  -- string COMMENT ''分类分组
 ,view_name  -- string COMMENT ''视图名称
 ,product_level_names  -- string COMMENT ''产品层次名称
 ,purchase_tech_types  -- string COMMENT ''采购技术要求
 ,oid  -- string COMMENT ''文件夹oid
 ,is_zk  -- string COMMENT ''该分类是否为重卡分类
from 
sany_data.ods_mdm_tm_clas_df -- mini_size: tm_clas_id
where ds = '${IncStartDate}' 
and hide = 'N' 
and status in ('30','50')) t1 

left join ( SELECT
 tm_clas_id
 ,no
 ,descs
FROM sany_data.ods_mdm_tm_clas_df -- mini_size: tm_clas_id
WHERE ds = '${IncStartDate}' 
and hide = 'N' 
and status in ('30','50') 
and depth = 1 ) t2 
on t1.level1_id = t2.tm_clas_id 

left join ( SELECT
 tm_clas_id
 ,no
 ,descs
FROM sany_data.ods_mdm_tm_clas_df -- mini_size: tm_clas_id
WHERE ds = '${IncStartDate}' 
and hide = 'N' 
and status in ('30','50') 
and depth = 2 ) t3 
on t1.level2_id = t3.tm_clas_id 


left join ( SELECT
 tm_clas_id
 ,no
 ,descs
FROM sany_data.ods_mdm_tm_clas_df -- mini_size: tm_clas_id
WHERE ds = '${IncStartDate}' 
and hide = 'N' 
and status in ('30','50') 
and depth = 3 ) t4
on t1.level3_id = t4.tm_clas_id 


left join ( SELECT
 tm_clas_id
 ,no
 ,descs
FROM sany_data.ods_mdm_tm_clas_df -- mini_size: tm_clas_id
WHERE ds = '${IncStartDate}' 
and hide = 'N' 
and status in ('30','50') 
and depth = 4 ) t5
on t1.level4_id = t5.tm_clas_id
;


-- 仅保留最近三个月的数据
ALTER TABLE sany_dim.dim_prd_mtrl_clss_df DROP PARTITION(ds <= '${Inc_Month_3}' );